easyexcel写入文件工具类

您所在的位置：网站首页 › java 写入 › easyexcel写入文件工具类

easyexcel写入文件工具类

2023-05-20 13:16| 来源: 网络整理| 查看: 265

工具类主要用于不同业务场景不同字段导出，动态头导出。其他简单场景原生的即可。

public class EasyExcelUtil { /** * 默认头样式 * * @return WriteCellStyle */ public static WriteCellStyle getHeadStyle() { // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 字体 WriteFont headWriteFont = new WriteFont(); //设置字体名字 headWriteFont.setFontName("宋体"); //设置字体大小 headWriteFont.setFontHeightInPoints((short) 11); //字体加粗 headWriteFont.setBold(true); //在样式用应用设置的字体; headWriteCellStyle.setWriteFont(headWriteFont); //设置底边框; headWriteCellStyle.setBorderBottom(BorderStyle.THIN); //设置底边框颜色; headWriteCellStyle.setBottomBorderColor((short) 0); //设置左边框; headWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置左边框颜色; headWriteCellStyle.setLeftBorderColor((short) 0); //设置右边框; headWriteCellStyle.setBorderRight(BorderStyle.THIN); //设置右边框颜色; headWriteCellStyle.setRightBorderColor((short) 0); //设置顶边框; headWriteCellStyle.setBorderTop(BorderStyle.THIN); //设置顶边框颜色; headWriteCellStyle.setTopBorderColor((short) 0); //设置自动换行; headWriteCellStyle.setWrapped(false); //设置水平对齐的样式为居中对齐; headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return headWriteCellStyle; } /** * 默认行样式 * * @return WriteCellStyle */ public static WriteCellStyle getBodyStyle() { WriteCellStyle bodyWriteCellStyle = new WriteCellStyle(); //设置底边框; bodyWriteCellStyle.setBorderBottom(BorderStyle.THIN); //设置底边框颜色; bodyWriteCellStyle.setBottomBorderColor((short) 0); //设置左边框; bodyWriteCellStyle.setBorderLeft(BorderStyle.THIN); //设置左边框颜色; bodyWriteCellStyle.setLeftBorderColor((short) 0); //设置右边框; bodyWriteCellStyle.setBorderRight(BorderStyle.THIN); //设置右边框颜色; bodyWriteCellStyle.setRightBorderColor((short) 0); //设置顶边框; bodyWriteCellStyle.setBorderTop(BorderStyle.THIN); //设置顶边框颜色; bodyWriteCellStyle.setTopBorderColor((short) 0); //设置自动换行; bodyWriteCellStyle.setWrapped(false); //设置水平对齐的样式为居中对齐; bodyWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); //设置垂直对齐的样式为居中对齐; bodyWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return bodyWriteCellStyle; } /** * 获取头行默认样式 * * @return HorizontalCellStyleStrategy */ public static HorizontalCellStyleStrategy getDefaultStyle() { return new HorizontalCellStyleStrategy(EasyExcelUtil.getHeadStyle(), EasyExcelUtil.getBodyStyle()); } /** * 写入excel文件 * * @param c class * @param data data * @param filePath filePath */ public static void write(Class c, List data, String filePath) { EasyExcel.write(filePath, c) .registerWriteHandler(getDefaultStyle()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet("导出数据") .doWrite(data); } public static void write(Class c, List data, String filePath, String sheetName) { EasyExcel.write(filePath, c) .registerWriteHandler(getDefaultStyle()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet(sheetName) .doWrite(data); } /** * 写入excel文件--指定列 * * @param includeColumnFieldNames includeColumnFieldNames * @param c class * @param data data * @param filePath filePath */ public static void write(Class c, Set includeColumnFieldNames, List data, String filePath) { EasyExcel.write(filePath, c) .registerWriteHandler(getDefaultStyle()) .includeColumnFieldNames(includeColumnFieldNames) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet("导出数据") .doWrite(data); } public static void write(Class c, Set includeColumnFieldNames, List data, String filePath, String sheetName) { EasyExcel.write(filePath, c) .registerWriteHandler(getDefaultStyle()) .includeColumnFieldNames(includeColumnFieldNames) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .sheet(sheetName) .doWrite(data); } /** * 写入excel文件--动态头&指定列 * * @param includeColumnFieldNames includeColumnFieldNames * @param headTitleProperties headTitleProperties * @param c class * @param data data * @param filePath filePath */ public static void write(Class c, Set includeColumnFieldNames, Properties headTitleProperties, List data, String filePath) { EasyExcel.write(filePath, c) .registerWriteHandler(getDefaultStyle()) //动态标题 .includeColumnFieldNames(includeColumnFieldNames) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(new TitleHandler(headTitleProperties)) .sheet("导出数据") .doWrite(data); } public static void write(Class c, Set includeColumnFieldNames, Properties headTitleProperties, List data, String filePath, String sheetName) { EasyExcel.write(filePath, c) .registerWriteHandler(getDefaultStyle()) //动态标题 .includeColumnFieldNames(includeColumnFieldNames) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(new TitleHandler(headTitleProperties)) .sheet(sheetName) .doWrite(data); } /** * 写入excel文件--多sheet * @param filePath filePath * @param sheets sheets * @throws Exception Exception */ public static void write(String filePath, WriteSheetWrapper... sheets) throws Exception { ExcelWriter excelWriter = EasyExcel.write(filePath) // 表头、内容样式设置 .registerWriteHandler(getDefaultStyle()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .autoCloseStream(Boolean.FALSE) .build(); for (int i = 0; i excelWriterSheetBuilder.registerWriteHandler(new TitleHandler(sheet.getDynamicTitles())); } if (null != sheet.getIncludeColumnFieldNames() && !sheet.getIncludeColumnFieldNames().isEmpty()) { excelWriterSheetBuilder.includeColumnFieldNames(sheet.getIncludeColumnFieldNames()); } excelWriter.write(sheet.getList(), excelWriterSheetBuilder.build()); } excelWriter.finish(); } /** * 导出excel * * @param response response * @param cls cls * @param fileName fileName * @param list list * @throws Exception Exception */ public static void download(HttpServletResponse response, Class cls, String fileName, List list) throws Exception { setExportHeader(response, fileName); EasyExcel.write(response.getOutputStream(), cls).registerWriteHandler(getDefaultStyle()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(fileName).doWrite(list); } /** * 导出excel--手工指定头 * * @param response response * @param headers headers * @param fileName fileName * @param list list * @throws Exception Exception */ public static void download(HttpServletResponse response, List headers, String fileName, List list) throws Exception { setExportHeader(response, fileName); EasyExcel.write(response.getOutputStream()).registerWriteHandler(getDefaultStyle()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).head(headers).sheet(fileName).doWrite(list); } /** * 公共导出数据方法-单sheet * * @param response response * @param c class * @param includeColumnFieldNames includeColumnFieldNames * @param data data * @param fileName fileName * @throws Exception Exception */ public static void download(HttpServletResponse response, Class c, Set includeColumnFieldNames, List data, String fileName, String sheetName) throws Exception { setExportHeader(response, fileName); ExcelWriterBuilder writer = EasyExcel.write(response.getOutputStream(), c)// 表头、内容样式设置 .registerWriteHandler(getDefaultStyle()) // 统一列宽,如需设置自动列宽则new LongestMatchColumnWidthStyleStrategy() .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) .autoCloseStream(Boolean.FALSE); // 这里需要设置不关闭流 if (!includeColumnFieldNames.isEmpty()) { writer.includeColumnFieldNames(includeColumnFieldNames); } writer.sheet(sheetName).doWrite(data); } /** * 公共导出数据方法-单sheet动态头 * * @param response response * @param c class * @param includeColumnFieldNames includeColumnFieldNames * @param headTitleProperties headTitleProperties * @param data data * @param fileName fileName * @throws Exception Exception */ public static void download(HttpServletResponse response, Class c, Properties headTitleProperties, Set includeColumnFieldNames, List data, String fileName, String sheetName) throws Exception { setExportHeader(response, fileName); ExcelWriterBuilder writer = EasyExcel.write(response.getOutputStream(), c)// 表头、内容样式设置 .registerWriteHandler(getDefaultStyle()) // 统一列宽,如需设置自动列宽则new LongestMatchColumnWidthStyleStrategy() .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) //动态标题 .registerWriteHandler(new TitleHandler(headTitleProperties)) .autoCloseStream(Boolean.FALSE); // 这里需要设置不关闭流 if (!includeColumnFieldNames.isEmpty()) { writer.includeColumnFieldNames(includeColumnFieldNames); } writer.sheet(sheetName).doWrite(data); } /** * 导出excel-多sheet * * @param response response * @param fileName fileName * @param sheets sheets * @throws Exception Exception */ public static void download(HttpServletResponse response, String fileName, WriteSheetWrapper... sheets) throws Exception { setExportHeader(response, fileName); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()) // 表头、内容样式设置 .registerWriteHandler(getDefaultStyle()) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .autoCloseStream(Boolean.FALSE) .build(); for (int i = 0; i excelWriterSheetBuilder.registerWriteHandler(new TitleHandler(sheet.getDynamicTitles())); } if (null != sheet.getIncludeColumnFieldNames() && !sheet.getIncludeColumnFieldNames().isEmpty()) { excelWriterSheetBuilder.includeColumnFieldNames(sheet.getIncludeColumnFieldNames()); } excelWriter.write(sheet.getList(), excelWriterSheetBuilder.build()); } excelWriter.finish(); } public static void setExportHeader(HttpServletResponse response, String fileName) throws Exception { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); } }

涉及到的两个类，一个是动态头处理TitleHandler，一个是多sheet包装

public class TitleHandler implements CellWriteHandler { Properties properties; PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("{", "}"); public TitleHandler(Properties properties) { this.properties = properties; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { if (null == head) { return; } List headNameList = head.getHeadNameList(); if (headNameList.isEmpty()) { return; } headNameList.replaceAll(value -> placeholderHelper.replacePlaceholders(value, properties)); } } public class WriteSheetWrapper { private String sheetName; private Properties dynamicTitles; private Class cls; private List list; private Collection includeColumnFieldNames; public String getSheetName() { return sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } public Properties getDynamicTitles() { return dynamicTitles; } public void setDynamicTitles(Properties dynamicTitles) { this.dynamicTitles = dynamicTitles; } public Class getCls() { return cls; } public void setCls(Class cls) { this.cls = cls; } public List getList() { return list; } public void setList(List list) { this.list = list; } public Collection getIncludeColumnFieldNames() { return includeColumnFieldNames; } public void setIncludeColumnFieldNames(Collection includeColumnFieldNames) { this.includeColumnFieldNames = includeColumnFieldNames; } public void buildDynamicTitles(String key, String value) { this.dynamicTitles = new Properties(); this.dynamicTitles.put(key, value); } public void addDynamicTitles(String key, String value) { this.dynamicTitles.put(key, value); } }

补充一下动态头的使用方法类上属性注解

@ExcelProperty(value={"{title}","属性1"})

调用函数时，将如下传入动态头参数headTitleProperties

Properties p = new Properties(); p.put("title","场景1"); EasyExcelUtil.write(bean.class,columns,p,list,filePath);

【本文地址】

easyexcel写入文件工具类

easyexcel写入文件工具类

今日新闻

推荐新闻